{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Banner](images/banner.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# python-oracledb Introduction"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This Jupyter Notebook shows how to use [python-oracledb](https://oracle.github.io/python-oracledb/) in its default 'Thin' mode that connects directly to Oracle Database."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# python-oracledb Connection"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Architecture\n",
    "\n",
    "Documentation reference link: [Introduction to python-oracledb](https://python-oracledb.readthedocs.io/en/latest/user_guide/introduction.html)\n",
    "\n",
    "![Architecture](images/architecture-thin.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Installation\n",
    "\n",
    "Documentation reference link: [python-oracledb Installation](https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Install python-oracledb**\n",
    "\n",
    "Install with a command like one of the following:\n",
    "\n",
    "```\n",
    "$ python3 -m pip install oracledb --upgrade\n",
    "$ python3 -m pip install oracledb --upgrade --user\n",
    "$ python3 -m pip install oracledb --upgrade --user --proxy=http://proxy.example.com:80\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To use python-oracledb, your application code can import the module:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import oracledb"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Connecting to a Database\n",
    "\n",
    "**Connections are used for executing SQL and PL/SQL in an Oracle Database**\n",
    "\n",
    "Documentation reference link: [Connecting to Oracle Database](https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Credentials\n",
    "un = \"pythondemo\"\n",
    "pw = \"welcome\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Instead of hard coding the password, you could prompt for a value, pass it as an environment variable, or use Oracle \"external authentication\"."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Easy Connect Syntax: \"hostname/servicename\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "cs = \"localhost/orclpdb1\"\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "print(connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Oracle Client 19c improved [Easy Connect Plus](https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-8C85D289-6AF3-41BC-848B-BF39D32648BA) syntax with additional optional settings, for example:\n",
    "\n",
    "```\n",
    "cs = \"tcps://my.cloud.com:1522/orclpdb1?connect_timeout=4&expire_time=10\"\n",
    "```\n",
    "\n",
    "<!-- See the [technical brief](https://download.oracle.com/ocomdocs/global/Oracle-Net-19c-Easy-Connect-Plus.pdf). -->"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Oracle Network and Oracle Client Configuration Files\n",
    "\n",
    "Oracle Database's `tnsnames.ora` file can be used.  This file maps a connect descriptor to an alias.  \n",
    "\n",
    "Documentation reference link: [Optional configuration files](https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#optional-oracle-net-configuration-files)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "\n",
    "# tnsnames.ora in /opt/oracle/configdir\n",
    "    \n",
    "highperfdb = (description= \n",
    "               (retry_count=5)(retry_delay=3)\n",
    "               (address=(protocol=tcps)(port=1522)(host=xxxxxx.oraclecloud.com))\n",
    "               (connect_data=(service_name=yyyyyyyyyy.oraclecloud.com))\n",
    "               (security=(ssl_server_cert_dn=\n",
    "                 \"CN=zzzzzzzz.oraclecloud.com,OU=Oracle ADB,O=Oracle Corporation,L=Redwood City,ST=California,C=US\")))\n",
    "                 "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Your Python code could use the alias as the connection `dsn` value:\n",
    "```\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=\"highperfdb\", config_dir=\"/opt/oracle/configdir\")\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connection Types"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standalone Connections\n",
    "\n",
    "Standalone connections are simple to create."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Stand-alone Connection](images/standalone-connection.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Stand-alone Connections\n",
    "\n",
    "connection = oracledb.connect(user=un, password=pw, dsn=cs)\n",
    "\n",
    "print(connection)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pooled Connections"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Pools are highly recommended if you have:\n",
    "- a lot of connections that will be used for short periods of time\n",
    "- or a small number of connections that are idle for long periods of time\n",
    "\n",
    "#### Pool advantages\n",
    "- Reduced cost of setting up and tearing down connections\n",
    "- Dead connection detection and automatic re-establishment"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![Pooled connection](images/pooled-connection.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Pooled Connections\n",
    "\n",
    "# Call once during application initization\n",
    "pool = oracledb.create_pool(user=un, password=pw, dsn=cs,\n",
    "                            min=1, max=10, increment=1)\n",
    "\n",
    "# Get a connection when needed in the application body\n",
    "with pool.acquire() as connection:\n",
    "    # do_something_useful(connection)\n",
    "    print(f\"Got a connection to Oracle Database {connection.version}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Tip** Use a fixed size pool `min` = `max` and `increment = 0`.  See [Guideline for Preventing Connection Storms: Use Static Pools](https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-7DFBA826-7CC0-4D16-B19C-31D168069B54)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Closing Connections"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Close connections when not needed.  This is important for pooled connections.\n",
    "\n",
    "```\n",
    "connection.close()\n",
    "```\n",
    "\n",
    "To avoid resource closing order issues, you may want to use `with` or let resources be closed at end of scope:\n",
    "\n",
    "```\n",
    "with pool.acquire() as connection:\n",
    "    do_something(connection)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Database Resident Connection Pooling\n",
    "\n",
    "**Connection pooling on the database tier**\n",
    "\n",
    "Documentation reference link: [Database Resident Connection Pooling (DRCP)](https://python-oracledb.readthedocs.io/en/latest/user_guide/connection_handling.html#database-resident-connection-pooling-drcp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Dedicated server processes are the default in the database, but DRCP is an alternative when the database server is short of memory."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![DRCP architecture](images/drcp-architecture.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Use DRCP if and only if:\n",
    "- The database computer doesn't have enough memory for all the server processes for all open application connections\n",
    "- When you have thousands of users which need access to a database server session for a short period of time\n",
    "- Applications mostly use same database credentials, and have identical session settings\n",
    "\n",
    "Using DRCP in conjunction with a python-oracledb connection pool is recommended."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Memory example with 5000 application users and a DRCP pool of size 100\n",
    "![DRCP memory comparison](images/drcp-comparison.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In Python, the connect string must request a pooled server, for example with ':pooled'.  For best efficiency to allow database server session re-use, set a connection class and use the purity 'PURITY_SELF'.\n",
    "\n",
    "```\n",
    "pool = oracledb.create_pool(user=un, password=pw, dsn=\"dbhost.example.com/orclpdb1:pooled\",\n",
    "                            cclass=\"MYCLASS\", purity=oracledb.PURITY_SELF)\n",
    "\n",
    "connection = pool.acquire()\n",
    "```\n",
    "\n",
    "Don't forget to start the pool first!:\n",
    "```\n",
    "SQL> execute dbms_connection_pool.start_pool()\n",
    "```\n",
    "\n",
    "Note DRCP is pre-enabled on Oracle Autonomous Database."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
